﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTHLSTAData : BaseFunction
    {
        
        public JLTHLSTAData()
            : base()
        {

        }
        public JLTHLSTAData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVK02(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT STAFF,KDKNAME,SUM(TOTQTY) AS TOTQTY,SUM(FGREQTY+FCREQTY) AS FREQTY, "
                    + " SUM(NEWQTY) AS NEWQTY,SUM(CREQTY) AS CREQTY,SUM(NORQTY) AS NORQTY, "
                    + " (case when SUM(FGREQTY + FCREQTY) >SUM(NEWQTY) + SUM(CREQTY) then 0 "
                    + "  else SUM(NEWQTY) + SUM(CREQTY) - SUM(FGREQTY + FCREQTY)end) AS ONCEQTY, "
                    + "  ROUND(SUM(NORQTY) / SUM(TOTQTY), 2) * 100 || '%' AS NORRATE, "
                    + " (case when SUM(FGREQTY + FCREQTY)>(SUM(NEWQTY) + SUM(CREQTY)) then "
                    + "  '0%' else (case when (SUM(NEWQTY) + SUM(CREQTY))=0 then 'N/A' ELSE "
                    + "  ROUND((SUM(NEWQTY) + SUM(CREQTY) - SUM(FGREQTY + FCREQTY)) / "
                    + "  (SUM(NEWQTY) + SUM(CREQTY)),2) * 100 || '%' END)end) AS ONCERATE "
                    + " FROM (SELECT A.VKPSACHBE,TRIM(C.HRNNAME) AS STAFF, "
                    + " B.KDKNAME,1 AS TOTQTY,DECODE(A.VKPPOSTYP,'NEW',1,0) AS NEWQTY, "
                    + " DECODE(A.VKPPOSTYP,'REG',1,0) AS FGREQTY,DECODE(A.VKPPOSTYP,'REC',1,0) AS FCREQTY, "
                    + " DECODE(A.VKPPOSTYP,'REH',1,0) AS CREQTY, "
                    + " (CASE WHEN VKPLIEFTERM >= TO_CHAR(SYSDATE,'YYYYMMDD') THEN 1 "
                    + " ELSE (case when A.VKPVERZTERM3 - A.VKPLIEFTERM >= 0 THEN "
                    + " 1 ELSE 0 END) end )AS NORQTY, "
                    + " DECODE(A.VKPLIEFTERM,0,'UNTIME','TIMEING') AS SCHE "
                    + " FROM VKPOS A,KDSTAMM B,HRSTAMM C "
                    + " WHERE A.VKPSA <>'AJ' AND (A.VKPAUFKRS LIKE 'H%' OR A.VKPAUFKRS LIKE 'Y%') "
                    + " AND A.VKPMANDANT = B.KDMANDANT AND A.VKPKUNDNR = B.KDKUNDNR "
                    + " AND A.VKPMANDANT = C.HRMANDANT AND A.VKPSACHBE = C.HRBENID "
                    + " AND A.VKPLIEFTERM >" + FROMDNDATE + " AND A.VKPLIEFTERM <=" + ENDDNDATE + " "
                    + " AND A.VKPNEUDAT>=" + FROMDATE + " AND A.VKPNEUDAT <=" + ENDDATE + " )  "
                    + " GROUP BY STAFF,KDKNAME  order by 1,2 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STAFF"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STAFF"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDKNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDKNAME"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTHLSTAData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVK02(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}